package cn.gson.zuche.model.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.zuche.model.bean.User;
import cn.gson.zuche.model.bean.Userorder;
import cn.gson.zuche.model.jdbc.MySqlDb;
import cn.gson.zuche.model.jdbc.ParamHandler;
import cn.gson.zuche.model.jdbc.ResultHandler;

public class Userorderdao implements ResultHandler<Userorder> {
	private MySqlDb db = MySqlDb.getInstance();

	public List<Userorder> select(String page) throws SQLException {
		String sql = "select order_paixu,order_single_time,order_id,order_zt_name as user_order_zt,order_money,user_tel,user_name AS user_id,user_sfz,order_car_type from `user`,user_order,user_order_zt WHERE user_order_zt.order_zt_id=user_order.user_order_zt and  user.user_id=user_order.user_id   ORDER BY order_paixu,order_single_time DESC   limit "+page+",4";
		return db.executeQuery(sql, this);
	}
	/**
	 * 查询不同状态下的订单
	 */
	public List<Userorder> selectdindan(String zt) throws SQLException {
		String sql = "select order_single_time,order_id,user_order_zt as s,order_zt_name as user_order_zt,order_money,user_tel,user_name AS user_id,order_car_type from `user`,user_order,user_order_zt WHERE user_order_zt.order_zt_id=user_order.user_order_zt and user.user_id=user_order.user_id and user_order_zt='"+zt+"' order by order_single_time  DESC";
		return db.executeQuery(sql, this);
	}
	/**
	 * 查询所有完成的订单id
	 */
	public List<Userorder> selectwcdindanid(String zt) throws SQLException {
		String sql = " SELECT order_id from user_order where  user_order_zt='"+zt+"'";
		return db.executeQuery(sql, this);
	}
	/**
	 * 查询所有完成订单的金钱
	 */
	public List<Userorder> selectwcdindanmoney(String l) throws SQLException {
		String sql = " select order_single_time,user_order.order_id,user_order_zt as s,order_zt_name as user_order_zt,SUM(order_xiaofeijilu.order_money) as order_money,user_tel,user_name AS user_id,order_car_type from  order_xiaofeijilu,`user`,user_order,user_order_zt WHERE user_order_zt.order_zt_id=user_order.user_order_zt and user.user_id=user_order.user_id  and order_xiaofeijilu.order_id=user_order.order_id and order_xiaofeijilu.order_id='"+l+"'";		    
		return db.executeQuery(sql, this);
	}
	
	
/**
 * 求出所有的记录数
 * @param id
 * @return
 * @throws SQLException
 */
	public List<Userorder> selectcount() throws SQLException {
		String sql = "select COUNT(order_id) as order_id from user_order";
		return db.executeQuery(sql, this);
	}
	
	public List<Userorder> selectyige(String id,Integer page,String zt) throws SQLException {
		StringBuffer sql = new StringBuffer("select order_paixu,order_single_time,order_id,order_zt_name as user_order_zt,order_money,user_tel,user_name AS user_id,user_sfz,order_car_type from `user`,user_order,user_order_zt WHERE user_order_zt.order_zt_id=user_order.user_order_zt and  user.user_id=user_order.user_id");
		if(zt!=null||"".equals(zt)){
			String and=" and user_order_zt=(SELECT order_zt_id from user_order_zt WHERE order_zt_name like '%"+zt+"%')";
			sql.append(and);
		}		
		if(id!=null){
		String or=" and (user_tel like '%"+id+"%' or user_order.user_id=(SELECT user_id from user where user_name like '%"+id+"%'))";	
		sql.append(or);
		}	
		String order=" ORDER BY order_paixu,order_single_time DESC limit "+page+",4";
		sql.append(order);
		System.out.println(sql.toString());
		return db.executeQuery(sql.toString(), this);
	}
	public List<Userorder> selectdaicangshumu(String id,Integer page,String zt) throws SQLException {
		StringBuffer sql =new StringBuffer( "select order_single_time,COUNT(order_id) as order_id,order_zt_name as user_order_zt,order_money,user_tel,user_name AS user_id,user_sfz from `user`,user_order,user_order_zt WHERE user_order_zt.order_zt_id=user_order.user_order_zt and  user.user_id=user_order.user_id");
		if(id!=null){
			String or=" and (user_tel like '%"+id+"%' or user_order.user_id=(SELECT user_id from user where user_name like '%"+id+"%'))";	
			sql.append(or);
		}
		if(zt!=null&&!"".equals(zt)){
			String and=" and user_order_zt=any(SELECT order_zt_id from user_order_zt WHERE order_zt_name like '%"+zt+"%')";
			sql.append(and);
		}	
		return db.executeQuery(sql.toString(), this);
	}
/**
 * 给用户分配车
 * @param id
 * @return
 * @throws SQLException
 */	
	public boolean fengche(String dindanid,String chepaihao) throws SQLException {
		String sql = "UPDATE user_order set order_car_id='"+chepaihao+"' WHERE order_id='"+dindanid+"'";
		return db.executeUpdate(sql) > 0;
	}
	/**
	 * 修改订单状态
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public boolean updatedindanztai(String id) throws SQLException {
		String sql = "update user_order set user_order_zt='6' where order_id='" + id + "'";
		return db.executeUpdate(sql) > 0;
	}
	/**
	 * 退款后订单状态
	 */
	public boolean updatetuikuanzt(String id,String zt) throws SQLException {
		String sql = "update user_order set user_order_zt='"+zt+"' where order_id='" + id + "'";

		return db.executeUpdate(sql) > 0;
	}
	/**
	 * 所有商品信息
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> suoyouxinxi(String id) throws SQLException {
		String sql = "select order_car_id,order_car_type,car_price from car_type,user_order WHERE order_id='"+id+"' and car_type_name=(SELECT order_car_type FROM user_order WHERE order_id='"+id+"')";
		return db.executeQuery(sql, this);
	}
	
	public boolean updatezt(String id) throws SQLException {
		String sql = "update user_order set user_order_zt='1' where order_id='" + id + "'";
		return db.executeUpdate(sql) > 0;

	}

	public boolean updata(String id, String money) throws SQLException {
		String sql = "update user_order SET order_money=" + money + " WHERE order_id='" + id + "'";
		return db.executeUpdate(sql) > 0;
	}

	/**
	 * 用户中心分页查询所有订单
	 * 
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> ownfindorder(String userid,String page) throws SQLException {
		String sql = "select order_id,order_zt_name as user_order_zt,order_money,order_gettime,order_backtime,order_getaddress,order_backaddress,order_car_type "
				+ "from user_order,user_order_zt WHERE user_id=" + userid
				+ " AND user_order_zt.order_zt_id=user_order.user_order_zt ORDER BY zt_paixu limit "+page+",4";
		return db.executeQuery(sql, this);
	}
	public List<Userorder> findAllorder (String userid) throws SQLException{
		String sql = "select * from user_order where user_id = '"+userid+"'";
		return db.executeQuery(sql, this);
	}
	/**
	 * 用户中心查询待付款订单
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> ownfinddfkorder(String userid) throws SQLException {
		String sql = "select order_id,order_zt_name as user_order_zt,order_money,order_gettime,order_backtime,order_getaddress,order_backaddress,order_car_type,order_single_time "
				+ "from user_order,user_order_zt WHERE user_id=" + userid
				+ " AND(user_order_zt=4) AND user_order_zt.order_zt_id=user_order.user_order_zt";
		return db.executeQuery(sql, this);
	}
	/**
	 * 用户中心查询已完成订单
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> ownfindywcorder(String userid) throws SQLException {
		String sql = "select order_id,order_zt_name as user_order_zt,order_money,order_gettime,order_backtime,order_getaddress,order_backaddress,order_car_type "
				+ "from user_order,user_order_zt WHERE user_id=" + userid
				+ " AND(user_order_zt=1) AND user_order_zt.order_zt_id=user_order.user_order_zt";
		return db.executeQuery(sql, this);
	}
	/**
	 * 用户中心查询已取消订单
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> ownfindYqxorder(String userid) throws SQLException {
		String sql = "select order_id,order_zt_name as user_order_zt,order_money,order_gettime,order_backtime,order_getaddress,order_backaddress,order_car_type "
				+ "from user_order,user_order_zt WHERE user_id=" + userid
				+ " AND(user_order_zt=3) AND user_order_zt.order_zt_id=user_order.user_order_zt";
		return db.executeQuery(sql, this);
	}
	/**
	 * 用户中心查询租赁中订单
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> ownfindZlzorder(String userid) throws SQLException {
		String sql = "select order_id,order_zt_name as user_order_zt,order_money,order_gettime,order_backtime,order_getaddress,order_backaddress,order_car_type "
				+ "from user_order,user_order_zt WHERE user_id=" + userid
				+ " AND(user_order_zt=6) AND user_order_zt.order_zt_id=user_order.user_order_zt";
		return db.executeQuery(sql, this);
	}
	/**
	 * 用户中心查询处理中订单
	 * @param userid
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> ownfindclzorder(String userid) throws SQLException {
		String sql = "select order_id,order_zt_name as user_order_zt,order_money,order_gettime,order_backtime,order_getaddress,order_backaddress,order_car_type "
				+ "from user_order,user_order_zt WHERE user_id=" + userid
				+ " AND(user_order_zt=5 or user_order_zt=7 or user_order_zt=8) AND user_order_zt.order_zt_id=user_order.user_order_zt";
		return db.executeQuery(sql, this);
	}
	
	/**
	 * 用户退单
	 * 
	 * @param orderid
	 * @return
	 * @throws SQLException
	 */
	public boolean centerQuitoeder(String orderid) throws SQLException {
		String sql = "update user_order set user_order_zt='3' where order_id='" + orderid + "'";
		return db.executeUpdate(sql) > 0;
	}

	/**
	 * 根据订单id查询订单所有信息
	 * 
	 * @param orderid
	 * @return
	 * @throws SQLException
	 */
	public List<Userorder> findAllByorderId(String orderid) throws SQLException {
		String sql = "select * from user_order where order_id='" + orderid + "'";
		return db.executeQuery(sql, this);
	}

	/**
	 * 用户付款 修改订单状态 为已付款。
	 * 
	 * @param orderid
	 * @return
	 * @throws SQLException
	 */
	public boolean payorderbyid(String orderid) throws SQLException {
		String sql = "update user_order set user_order_zt='5' where order_id='" + orderid + "'";
		return db.executeUpdate(sql) > 0;
	}

	/**
	 * 用户还车按钮
	 * 
	 * @param orderid
	 * @return
	 * @throws SQLException
	 */
	public boolean hcbyorderid(String orderid) throws SQLException {
		String sql = "update user_order set user_order_zt='7' where order_id='" + orderid + "'";
		return db.executeUpdate(sql) > 0;
	}
	
	
	/**
	 * 插入订单
	 * @return
	 * @throws SQLException
	 */
	public boolean saveorder(final Userorder ud) throws SQLException{
		String sql = "INSERT into user_order(order_id,order_gettime,order_backtime,order_car_siji,order_getaddress,order_backaddress,user_order_zt,order_money,order_car_type,user_id,order_mianpei,order_single_time,order_yhq_id,order_lxrname,order_lxrphone,order_fp) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		return db.executeUpdate(sql,new saveorder(ud))>0;
}
	
	
	public List findokorderbyuserid(Long userid) throws SQLException {
		String sql = "SELECT order_id FROM user_order WHERE user_id='"+userid+"' AND user_order_zt='1'";
		return db.executeQuery(sql,new ResultHandler(){

			@Override
			public Object doHander(Map row) {
				return row.get("order_id");
			}});
	}
	public boolean quitmoney(String orderid) throws SQLException {
		String sql = "update user_order set user_order_zt='8' where order_id='" + orderid + "'";
		return db.executeUpdate(sql)>0;
		
	}

	private class saveorder implements ParamHandler{
			Userorder ud;
			saveorder(Userorder ud){
				this.ud=ud;
			}
			@Override
			public void doHander(PreparedStatement pStatement) throws SQLException {
				pStatement.setString(1, ud.getOrderid());
				pStatement.setObject(2, ud.getGettime());
				pStatement.setObject(3, ud.getBacktime());		
				pStatement.setString(4,	ud.getOrdercarsiji());	
				pStatement.setString(5,	ud.getOrdergetaddress());
				pStatement.setString(6,	ud.getOrderbackaddress());
				pStatement.setString(7,	ud.getUserorderzt());
				pStatement.setString(8, ud.getOdermoney());
				pStatement.setString(9, ud.getOrdercartype());
				pStatement.setString(10, ud.getUserid());
				pStatement.setString(11, ud.getOrdermianpei());
				pStatement.setObject(12, ud.getOrdersingletime());
				if(ud.getOrderyhqid()!=null){
					pStatement.setInt(13, ud.getOrderyhqid());
				}else{
					pStatement.setInt(13,0);
				}
				pStatement.setString(14, ud.getOrderlxrname());
				pStatement.setString(15, ud.getOrderlxrphone());
				pStatement.setString(16, ud.getOrderfp());
			}
			
	}
	@Override
	public Userorder doHander(Map<String, Object> row) {
		Userorder order = new Userorder();
		order.setBacktime(row.get("order_backtime"));
		order.setGettime(row.get("order_gettime"));
		order.setOdermoney(row.get("order_money")+"");
		order.setOrderid(row.get("order_id")+"");
		order.setOrderbackaddress((String) row.get("order_backaddress"));
		order.setOrdercarid((String) row.get("order_car_id"));
		order.setOrdercommentid((Integer) row.get("order_comment_id"));
		order.setOrderdelayid((Integer) row.get("order_delay_id"));
		order.setOrderdriverid((String) row.get("order_driver_id"));
		order.setOrdergetaddress((String) row.get("order_getaddress"));
		order.setOrderstoreid((String) row.get("order_store_id"));
		order.setOrderyhqid((Integer) row.get("order_yhq_id"));
		order.setUserid(row.get("user_id") + "");
		order.setOrdermianpei((String) row.get("order_mianpei"));
		order.setUserorderzt(row.get("user_order_zt") + "");
		order.setUsertel((String) row.get("user_tel"));
		order.setUsersfz((String) row.get("user_sfz"));
		order.setOrdercartype((String) row.get("order_car_type"));
		order.setPrice((String) row.get("car_price"));
		order.setOrdercarsiji((String) row.get("order_car_siji"));
		order.setOrdersingletime(row.get("order_single_time"));
		return order;
	}

	public List<Userorder> findxinxi(String ordernum)throws SQLException {
		String sql = "select * from user_order where order_id='"+ordernum+"'";
		return db.executeQuery(sql, this);
	}

	

}
